package com.xxl.job.executor.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xxl.job.core.context.XxlJobHelper;
import com.xxl.job.executor.entity.Cell;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.cxf.endpoint.Client;
import org.apache.cxf.jaxws.endpoint.dynamic.JaxWsDynamicClientFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/**
 * @author dingfeng
 * @version 1.0
 * @dec 报表处理
 * @date 2022/3/21 14:23
 */
@Component
@Slf4j
public class ReportUtil {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private PlatformTransactionManager transactionManager;
    private static final String CONTROL_REPORT_TABLE_INFO_SQL = "SELECT * FROM control_report_table_info WHERE table_name = ?";
    private static final String TABLE_CONSTRUCT_SQL = "SELECT * FROM information_schema.columns WHERE table_schema = ? AND table_name = ?";
    private static final String DELETE_BY_DAY = " where tyear = ? and tmonth = ? and tday = ?";
    private static final String DELETE_BY_MONTH = " where tyear = ? and tmonth = ?";
    private static final String DELETE_BY_YEAR = " where tyear = ?";
    private final ExecutorService mainExecutorService = Executors.newFixedThreadPool(7);
    private final CopyOnWriteArrayList<Future<Result>> allResult = new CopyOnWriteArrayList();

    public void handler(final String params) {
        String[] tableNames = params.split(",");
        for (String tableName : tableNames) {
            Future<Result> resultFuture = mainExecutorService.submit(new Task(tableName));
            allResult.add(resultFuture);
        }
        //失败主任务结果集合
        List<String> errorTables = new ArrayList(Arrays.asList(tableNames));
        while (allResult.size() > 0) {
            Future<Result> resultFuture = allResult.remove(0);
            try {
                Result result = resultFuture.get();
                if (result.code == 200) {
                    log.info("表[{}]插入成功,耗时{}ms", result.getTableName(), result.getTime());
                    errorTables.remove(result.getTableName());
                } else {
                    log.error("表[{}]插入失败,原因{}", result.getTableName(), result.getMes());
                }
            } catch (Exception e) {
                log.error("插入出错", e);
            }
        }
        if (CollectionUtils.isEmpty(errorTables)) {
            XxlJobHelper.handleSuccess("所有表插入成功");
            return;
        }
        XxlJobHelper.handleFail("未插入成功表名:" + errorTables);
    }

    /**
     * 任务处理
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Accessors(chain = true)
    class Task implements Callable<Result> {
        private String tableName;

        @Override
        public Result call() throws Exception {
            // 创建事务定义对象
            DefaultTransactionDefinition def = new DefaultTransactionDefinition();
            // 设置是否只读，false 支持事务
            def.setReadOnly(false);
            // 设置事务传播行为，必须有事务
            def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
            // 配置事务管理器
            TransactionStatus status = transactionManager.getTransaction(def);

            Long startTime = null;
            Result result = null;
            Long endTime = null;
            try {
                startTime = System.currentTimeMillis();
                result = doHandler(tableName);
                endTime = System.currentTimeMillis();
                // 提交事务
                transactionManager.commit(status);
            } catch (Exception e) {
                // 回滚事务
                transactionManager.rollback(status);
                throw e;
            }
            return result.setTime(endTime - startTime);
        }


        /**
         * @param tableName 表名称(包含模式名)
         * @return 任务执行结果
         * @throws Exception ..
         */
        public Result doHandler(final String tableName) throws Exception {
            log.info("表[{}]开始处理", tableName);
            //获取表名
            if (org.apache.commons.lang3.StringUtils.isBlank(tableName)) {
                log.error("表名不能为空!!!");
                return new Result().setCode(400).setMes("表名不能为空");
            }
            //1.根据表名查询接口信息
            List<Map<String, Object>> stringObjectMap = null;
            stringObjectMap = getInterfaceInfoByTableName(tableName.trim());
            if (stringObjectMap.isEmpty()) {
                return new Result().setCode(400).setMes("表[" + tableName + "]未找到").setTableName(tableName);
            }
            // 执行前语句
            handleBefore(stringObjectMap.get(0));
            for (int i = 0; i < stringObjectMap.size(); i++) {
                //请求接口
                System.out.println(stringObjectMap.get(i));
                JSONObject res = doWebServiceRequest(stringObjectMap.get(i));
                Assert.notNull(res, "webService返回的结果res为空");
                //获取表结构
                List<Map<String, Object>> tableConstruct = getTableConstructByTableName(tableName);
                //包装数据
                List<Object[]> batchData = getBatchData(res, tableConstruct);
                //拼接sql
                String resSql = appendSql(tableName, tableConstruct);
                //执行前语句
                //数据入库
                jdbcTemplate.batchUpdate(resSql, batchData);
                log.info("插入【{}】表共计{}条数据", tableName, batchData.size());
            }
            return new Result().setCode(200).setMes("插入成功").setTableName(tableName);
        }

        private void handleBefore(Map map) {
            String tableName = map.get("table_name").toString();

            String auto = map.get("auto").toString();

            Integer tYear = 0, tMonth = 0, tDay = 0;
            switch (auto) {
                case "0":
                    log.info("【{}】：auto = 0  获取手动输入时间", tableName);
                    String arg0 = StringUtils.trimAllWhitespace(map.get("arg0").toString());
                    JSONObject arg0JsonObject = JSONObject.parseObject(arg0);
                    String rq = arg0JsonObject.get("reportDate").toString();
                    String[] split = rq.split("-");
                    for (int i = 0; i < split.length; i++) {
                        if (split[i].startsWith("0")) {
                            split[i] = split[i].replaceAll("0", "");
                        }
                    }
                    tYear = Integer.parseInt(split[0]);
                    tMonth = Integer.parseInt(split[1]);
                    tDay = Integer.parseInt(split[2]);
                    break;
                case "1":
                    log.info("【{}】：auto = 1  自动获取时间", tableName);
                    Date date = new Date();
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String format = dateFormat.format(date);
                    String[] split2 = format.split("-");
                    for (int i = 0; i < split2.length; i++) {
                        if (split2[i].startsWith("0")) {
                            split2[i] = split2[i].replaceAll("0", "");
                        }
                    }
                    tYear = Integer.parseInt(split2[0]);
                    tMonth = Integer.parseInt(split2[1]);
                    tDay = Integer.parseInt(split2[2]);
                    break;
                default:
                    break;
            }


            String type = map.get("type").toString();
            boolean checkType = checkType(type);
            // 校验失败
            if (!checkType) {
                log.info("type 字段设置错误");
                throw new RuntimeException("type 字段设置错误");
            }
            // 校验成功
            String beforeSql = "delete from " + tableName;
            switch (type) {
                case "0":
                    log.info("【{}】： type = 0 日维度", tableName);
                    beforeSql += DELETE_BY_DAY;
                    jdbcTemplate.update(beforeSql, tYear, tMonth, tDay);
                    break;
                case "1":
                    log.info("【{}】： type = 1 月维度", tableName);
                    beforeSql += DELETE_BY_MONTH;
                    jdbcTemplate.update(beforeSql, tYear, tMonth);
                    break;
                case "2":
                    log.info("【{}】： type = 2 年维度", tableName);
                    beforeSql += DELETE_BY_YEAR;
                    jdbcTemplate.update(beforeSql, tYear);
                    break;
                default:
                    break;
            }
        }

        /**
         * 检验类型的合法性 只能为 0 1
         * 0 关闭
         * 1 开启
         *
         * @param auto
         * @return
         */
        private boolean checkAuto(String auto) {
            if (org.apache.commons.lang3.StringUtils.isBlank(auto)) {
                return false;
            }
            if ("0".equals(auto) || "1".equals(auto)) {
                return true;
            }
            return false;
        }

        /**
         * 检验类型的合法性 只能为 0 1 2
         * 0 day
         * 1 month
         * 2 year
         *
         * @param type
         * @return
         */
        private boolean checkType(String type) {
            if (org.apache.commons.lang3.StringUtils.isBlank(type)) {
                return false;
            }
            if ("0".equals(type) || "1".equals(type) || "2".equals(type)) {
                return true;
            }
            return false;
        }


        /**
         * 根据表名和表结构生成插入sql语句
         *
         * @param tableName      表名
         * @param tableConstruct 表结构
         * @return sql语句
         */
        private String appendSql(final String tableName, final List<Map<String, Object>> tableConstruct) {
            //表的列数量
            int columnSize = tableConstruct.size();
            HashMap<Integer, String> columns = new HashMap<>(32);
            for (Map<String, Object> map : tableConstruct) {
                int columnNumber = Integer.parseInt(map.get("ordinal_position").toString());
                String columnName = map.get("column_name").toString();
                columns.put(columnNumber, columnName);
            }
            StringBuilder sql = new StringBuilder("insert into ").append(tableName).append("(");
            StringBuilder sqlArgs = new StringBuilder(" values(");
            for (int i = 0; i < columnSize; i++) {
                String columnNames = columns.get(i + 1);
                String temp = "";
                if (i == columnSize - 1) {
                    temp = columnNames + ")";
                    sqlArgs.append("?)");
                } else {
                    temp = columnNames + ",";
                    sqlArgs.append("?,");
                }
                sql.append(temp);
            }
            sql.append(sqlArgs);
            return sql.toString();
        }

        /**
         * 根据api信息请求webService接口信息
         *
         * @param apiInfo 接口信息
         * @return 返回的数据 json
         * @throws Exception 调用接口异常 json解析异常
         */
        private JSONObject doWebServiceRequest(final Map<String, Object> apiInfo) throws Exception {
            //表名
            String tableName = apiInfo.get("table_name").toString();
            //参数一
            String arg0 = StringUtils.trimAllWhitespace(apiInfo.get("arg0").toString());
            //修改参数日期
            JSONObject arg0JsonObject = JSONObject.parseObject(arg0);

            // 检验是否自动获取时间字段
            String auto = apiInfo.get("auto").toString();
            boolean checkAuto = checkAuto(auto);

            // 校验失败
            if (!checkAuto) {
                log.info("auto 字段设置错误");
                throw new RuntimeException("auto 字段设置错误");
            }

            // 检验 时间维度
            String type = apiInfo.get("type").toString();
            boolean checkType = checkType(type);
            // 校验失败
            if (!checkType) {
                log.info("type 字段设置错误");
                throw new RuntimeException("type 字段设置错误");
            }
            if ("1".equals(auto)) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = new Date();
                switch (type) {
                    case "0":
                    case "2":
                        arg0JsonObject.put("reportDate", simpleDateFormat.format(date));
                        break;
                    case "1":
                        arg0JsonObject.put("reportDate", getLastDayOfCurrentMonth());
                        break;
                    default:
                        break;
                }
            }

            arg0 = arg0JsonObject.toString();
            //参数二
            String arg1 = StringUtils.trimAllWhitespace(apiInfo.get("arg1").toString());
            log.info("[{}]arg0 = {}", tableName, arg0);
            log.info("[{}]arg1 = {}", tableName, arg1);
            //接口地址
            String wsdlUrl = StringUtils.trimAllWhitespace(apiInfo.get("wsdl_url").toString());
            JaxWsDynamicClientFactory dcf = JaxWsDynamicClientFactory.newInstance();
            Client client = dcf.createClient(wsdlUrl);
            Object[] getOutRepData = client.invoke("getOutRepData", arg0, arg1);
//            Object[] getOutRepData = client.invoke("getOutRepData");
            //解析数据
            return (JSONObject) JSON.parse(getOutRepData[0].toString());
        }

        /**
         * 封装返回的数据到List
         *
         * @param res webService返回的结果
         * @return 批量数据集合
         */
        private List<Object[]> getBatchData(final JSONObject res, final List<Map<String, Object>> tableConstruct) {
            //表的列数量
            int columnSize = tableConstruct.size();
            JSONArray result = (JSONArray) res.get("result");
            JSONArray cellDataVos = null;
            if (null != result && result.size() > 0) {
                JSONObject resultItem = (JSONObject) result.get(0);
                cellDataVos = (JSONArray) resultItem.get("cellDataVos");
            }
            //根据行号分组后的数据
            Map<Integer, List<Cell>> dataGroupByRow = new HashMap(64);
            if (cellDataVos != null && cellDataVos.size() > 0) {
                List<Cell> cells = cellDataVos.toJavaList(Cell.class);
                dataGroupByRow = cells.stream().collect(Collectors.groupingBy(Cell::getRow));
            }
            log.info("webService接口返回数据{}条", dataGroupByRow.keySet().size());
            //封装数据
            List<Object[]> batchArgs = new ArrayList();
            for (Integer key : dataGroupByRow.keySet()) {
                //每一行的数据
                List<Cell> cells = dataGroupByRow.get(key);
                Object[] rowArgs = new Object[columnSize];
                for (Cell cell : cells) {
                    rowArgs[cell.getColumn() - 1] = cell.getValue();
                }
                batchArgs.add(rowArgs);
            }
            return batchArgs;
        }

        /**
         * 根据表名查询接口信息
         *
         * @param tableName 表名
         * @return
         * @throws Exception
         */
        private List<Map<String, Object>> getInterfaceInfoByTableName(final String tableName) {
            return jdbcTemplate.queryForList(CONTROL_REPORT_TABLE_INFO_SQL, tableName);
        }

        /**
         * 根据表名查询表结构
         *
         * @param tableName 表名
         * @return
         * @throws Exception
         */
        private List<Map<String, Object>> getTableConstructByTableName(final String tableName) {
            String[] schemaAndRealTableName = tableName.split("\\.");
            return jdbcTemplate.queryForList(TABLE_CONSTRUCT_SQL, schemaAndRealTableName[0], schemaAndRealTableName[1]);
        }

        /**
         * 获取本月最后一天
         *
         * @return "2022-03-31"
         */
        private String getLastDayOfCurrentMonth() {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = new Date();
            Calendar calendar = new GregorianCalendar();
            calendar.setTime(date);
            calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
            return sdf.format(calendar.getTime());
        }
    }

    /**
     *
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Accessors(chain = true)
    static class Result {
        private String tableName;
        private String mes;
        private Integer code;
        private Long time;
    }
}





